﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace CivilRegistry.Logger
{
    /// <summary>
    /// Object to interact with the database CivilDB.
    /// </summary>
    class CivilDB : IDisposable
    {
        /// <summary>
        /// Holds the sql connection.
        /// </summary>
        private SqlConnection cnn;

        /// <summary>
        /// Gets or sets the command type.
        /// </summary>
        public CommandType CommandType { get; set; }

        /// <summary>
        /// Initializes the connection string and opens the connection.
        /// </summary>
        /// <param name="connectionString">Connection String</param>
        public CivilDB(string connectionString)
        {
            cnn = new SqlConnection(connectionString);
            CommandType = System.Data.CommandType.StoredProcedure;
            Open();
        }

        /// <summary>
        /// Executes the specified command and returns the number of rows affected.
        /// </summary>
        /// <param name="command">Pass an sql command or stored procedure name.</param>
        /// <param name="parameters">Collection of SQL parameters.</param>
        /// <returns>Number of rows affected.</returns>
        public int ExecuteNonQuery(string command, List<SqlParameter> parameters)
        {
            using (SqlCommand cmd = new SqlCommand(command, cnn))
            {
                cmd.CommandType = this.CommandType;
                foreach (var pmtr in parameters)
                {
                    cmd.Parameters.Add(pmtr);
                }
                return cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Executes the specified command and returns the loaded instance of SqlDataReader.
        /// </summary>
        /// <param name="command">Pass an sql command or stored procedure name.</param>
        /// <param name="parameters">Collection of SQL parameters.</param>
        /// <returns>Return SQLDataReader</returns>
        public SqlDataReader ExecuteReader(string command, SqlParameterCollection parameters)
        {
            using (SqlCommand cmd = new SqlCommand(command))
            {
                cmd.CommandType = this.CommandType;
                foreach (var pmtr in parameters)
                {
                    cmd.Parameters.Add(pmtr);
                }
                return cmd.ExecuteReader();
            }
        }

        /// <summary>
        /// Opens the SQL Connection.
        /// </summary>
        public void Open()
        {
            if (cnn.State == ConnectionState.Closed)
                cnn.Open();
        }

        /// <summary>
        /// To dispose unmanaged resource.
        /// </summary>
        /// <param name="disposing">Pass True to Suppress the finalizer.</param>
        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (cnn.State == ConnectionState.Open)
                    cnn.Close();
                GC.SuppressFinalize(this);
            }
        }

        /// <summary>
        /// To dispose unmanaged resource.
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
        }

        /// <summary>
        /// Closes the SQL Connection
        /// </summary>
        public void Close()
        {
            Dispose(true);
        }

        /// <summary>
        /// Destruct the object CivilDB.
        /// </summary>
        ~CivilDB()
        {
            Dispose(false);
        }
    }
}
